﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
namespace C_sports_meet.Manager
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MJudge : Page
    {
        public MJudge()
        {
            InitializeComponent();
            DBconnection3 conn = new DBconnection3();
            DataTable dataset = conn.Select();
            if (dataset == null)
            {
                MessageBox.Show("没有数据");
            }
            DataGrid1.ItemsSource = dataset.DefaultView;
        }
        private void btnUpdate_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                DataRowView mySelectedElement = (DataRowView)DataGrid1.SelectedItem;
                string strCon2 = "server=.;database=sportmeet;user=sa;pwd=Lss200217";
                SqlConnection con2 = new SqlConnection(strCon2);
                string strSql2 = "delete  from judge where jno='" + mySelectedElement.Row[0].ToString() + "';";
                SqlCommand cmd2 = new SqlCommand(strSql2, con2);
                con2.Open();
                int j = cmd2.ExecuteNonQuery();
                con2.Close();
                string strCon = "server=.;database=sportmeet;user=sa;pwd=Lss200217";
                SqlConnection con = new SqlConnection(strCon);
                string strSql = "insert into judge(jno,jname,jsex,jage) values( '" + mySelectedElement.Row[0].ToString() + "','" + mySelectedElement.Row[1].ToString() + "','" + mySelectedElement.Row[2].ToString() + "','" + mySelectedElement.Row[3].ToString() + "')";
                SqlCommand cmd = new SqlCommand(strSql, con);
                con.Open();
                int i = cmd.ExecuteNonQuery();
                if (j + i == 2)
                {
                    MessageBox.Show("修改成功");
                }
                else
                {
                    MessageBox.Show("修改失败");
                }
                con.Close();
                DBconnection3 conn = new DBconnection3();
                DataTable dataset = conn.Select();
                if (dataset == null)
                {
                    MessageBox.Show("没有数据");
                }
                DataGrid1.ItemsSource = dataset.DefaultView;
            }
            catch
            {
                MessageBox.Show("操作失误，请重试。");
            }
        }

        private void btnDelete_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                DataRowView mySelectedElement = (DataRowView)DataGrid1.SelectedItem;
                string strCon2 = "server=.;database=sportmeet;user=sa;pwd=Lss200217";
                SqlConnection con2 = new SqlConnection(strCon2);
                string strSql2 = "delete  from judge where jno='" + mySelectedElement.Row[0].ToString() + "';";
                SqlCommand cmd2 = new SqlCommand(strSql2, con2);
                con2.Open();
                int j = cmd2.ExecuteNonQuery();
                if (j == 1)
                {
                    MessageBox.Show("删除成功");
                }
                else
                {
                    MessageBox.Show("删除失败");
                }
                con2.Close();
                DBconnection3 conn = new DBconnection3();
                DataTable dataset = conn.Select();
                if (dataset == null)
                {
                    MessageBox.Show("没有数据");
                }
                DataGrid1.ItemsSource = dataset.DefaultView;
            }
            catch
            {
                MessageBox.Show("操作失误，请重试。");
            }
        }

        private void inputAno_LostFocus(object sender, RoutedEventArgs e)
        {
            if (inputAno.Text.Trim() == "")
            {
                inputAno.Text = "请输入编号";
            }
        }

        private void inputAno_GotFocus(object sender, RoutedEventArgs e)
        {
            if (inputAno.Text.Trim() == "请输入编号")
            {
                inputAno.Text = "";
            }
        }

        private void inputAname_LostFocus(object sender, RoutedEventArgs e)
        {
            if (inputAname.Text.Trim() == "")
            {
                inputAname.Text = "请输入姓名";
            }
        }

        private void inputAname_GotFocus(object sender, RoutedEventArgs e)
        {
            if (inputAname.Text.Trim() == "请输入姓名")
            {
                inputAname.Text = "";
            }
        }

        private void btnInsert_Click(object sender, RoutedEventArgs e)
        {
            DataRowView mySelectedElement = (DataRowView)DataGrid1.SelectedItem;
            string strCon = "server=.;database=sportmeet;user=sa;pwd=Lss200217";
            SqlConnection con = new SqlConnection(strCon);
            string strSql = "";
            strSql = "select * from judge where jno='" + mySelectedElement.Row[0].ToString() + "'";
            SqlCommand cmd0 = new SqlCommand(strSql, con);
            con.Open();
            int count = Convert.ToInt32(cmd0.ExecuteScalar());
            con.Close();
            if (count > 0)
            {
                MessageBox.Show("请勿重复输入");
            }
            else if (mySelectedElement.Row[0].ToString() == null || mySelectedElement.Row[1].ToString() == null || mySelectedElement.Row[2].ToString() == null || mySelectedElement.Row[3].ToString() == null)
            {
                MessageBox.Show("添加项均不能为空");
            }
            else
            {
                strSql = "insert into judge(jno,jname,jsex,jage) values( '" + mySelectedElement.Row[0].ToString() + "','" + mySelectedElement.Row[1].ToString() + "','" + mySelectedElement.Row[2].ToString() + "','" + mySelectedElement.Row[3].ToString() + "')";
                SqlCommand cmd = new SqlCommand(strSql, con);
                con.Open();
                int i = cmd.ExecuteNonQuery();
                con.Close();
                if (i == 1)
                {
                    MessageBox.Show("添加成功");
                }
                else
                {
                    MessageBox.Show("添加失败");

                }
            }
        }

        private void btnSelect_Click(object sender, RoutedEventArgs e)
        {
            DBconnection3 conn = new DBconnection3(inputAno.Text);
            DataTable dataset = conn.Select();
            if (dataset == null)
            {
                MessageBox.Show("没有数据");
            }
            else
            {
                MessageBox.Show("参加比赛信息查询成功（若想增添，修改，删除信息请点击恢复初始状态）");
            }
            DataGrid1.ItemsSource = dataset.DefaultView;
        }

        private void btnFind_Click(object sender, RoutedEventArgs e)
        {
            DBconnection3 conn = new DBconnection3(inputAno.Text, inputAname.Text);
            DataTable dataset = conn.Select();
            if (dataset == null)
            {
                MessageBox.Show("没有数据");
            }
            else
            {
                MessageBox.Show("检索信息成功");
            }
            DataGrid1.ItemsSource = dataset.DefaultView;
        }
        private void btnRefresh_Click(object sender, RoutedEventArgs e)
        {
            DBconnection3 conn = new DBconnection3();
            DataTable dataset = conn.Select();
            if (dataset == null)
            {
                MessageBox.Show("没有数据");
            }
            DataGrid1.ItemsSource = dataset.DefaultView;
        }
    }

    class DBconnection3
    {
        private SqlConnection connection;
        public string jno = null;
        public string jname = null;
        public DBconnection3()
        {
            try
            {
                string strCon = "server=.;database=sportmeet;user=sa;pwd=Lss200217";
                connection = new SqlConnection(strCon);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        public DBconnection3(string jno)
        {
            try
            {
                string strCon = "server=.;database=sportmeet;user=sa;pwd=Lss200217";
                connection = new SqlConnection(strCon);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            this.jno = jno;
        }
        public DBconnection3(string jno, string jname)
        {
            try
            {
                string strCon = "server=.;database=sportmeet;user=sa;pwd=Lss200217";
                connection = new SqlConnection(strCon);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            this.jno = jno;
            this.jname = jname;
        }
        public DataTable Select()
        {
            try
            {
                string strSql = "";
                connection.Open();
                if (jno == null)
                {
                    strSql = "select jno 编号,jname 姓名,jsex 性别,jage 年龄 from judge";
                    SqlCommand cmd = new SqlCommand(strSql, connection);
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    return dt;
                }
                else if (jname == null && jno != null)
                {
                    strSql = "select eno 项目号,ename 项目名,etime 比赛时间,fno 类别号,jno 裁判编号,sno 学号,esex 参加者性别 from eevent where jno='" + jno + "'";
                    SqlCommand cmd1 = new SqlCommand(strSql, connection);
                    SqlDataAdapter sda1 = new SqlDataAdapter(cmd1);
                    DataTable dt1 = new DataTable();
                    sda1.Fill(dt1);
                    return dt1;
                }
                else
                {
                    strSql = "select jno 编号,jname 姓名,jsex 性别,jage 年龄 from judge where jno='" + jno + "'and jname='" + jname + "'";
                    SqlCommand cmd2 = new SqlCommand(strSql, connection);
                    SqlDataAdapter sda2 = new SqlDataAdapter(cmd2);
                    DataTable dt2 = new DataTable();
                    sda2.Fill(dt2);
                    return dt2;
                }

            }
            catch (Exception es)
            {
                throw new Exception(es.Message);
            }
            finally
            {
                connection.Close();
            }
        }
    }
}


